var express = require('express');
var router = express.Router();

const MyQuery = require('../utils/db')

//引入配置文件
const { API_URL } = require('../utils/config')

router.get('/demo2', function (req, res, next) {
    res.send('列表模块')
});

//地区数据接口
router.get('/areas', async (req, res, next) => {

    let { } = req.query

    let data = {}//最终要返回的数据格式  msg,status,result
    //1. 写sql
    let sql = `SELECT * FROM e_area `
    //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
})

//分类-学科接口
router.get('/subjects', async (req, res, next) => {

    let { } = req.query

    let data = {}//最终要返回的数据格式  msg,status,result
    //1. 写sql
    let sql = `SELECT * FROM e_subject `
    //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
})

//年级接口
router.get('/grades', async (req, res, next) => {

    let { } = req.query

    let data = {}//最终要返回的数据格式  msg,status,result
    //1. 写sql
    let sql = `SELECT * FROM e_grade `
    //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
})

//考试类别接口
router.get('/cates', async (req, res, next) => {
    let { } = req.query
    let data = {}//最终要返回的数据格式  msg,status,result
    //1. 写sql
    let sql = `SELECT * FROM e_category `
    //2. 执行sql
    let { result } = await MyQuery(sql) //{ err,result }

    //3. 要处理成规范、格式化的json数据
    data = {
        msg: 'OK',
        status: 200,
        result
    }
    res.send(data)
})

//课程列表接口
router.get('/courses', async (req, res, next) => {
    // let page = 2//要获取第几页的内容
    // let pageSize = 5//每页展示size条
    let { page = 1, pageSize = 5,area='',grade='',category='',subject='' } = req.query
    let data = {}//最终要返回的数据格式  msg,status,result

    // 1)数据处理
    page = page - 0 //parseInt / Number
    pageSize = pageSize - 0 //parseInt / Number
    // 2) 数据严格性
    if (!page || page <= 0) { //如果报错给默认值  
        page = 1
    }
    if (!pageSize || pageSize <= 0) {//如果报错给默认值
        pageSize = 5   
    }

    //分析分页
    let start = (page - 1) * pageSize //从第start条开始取

    /*
        条件筛选一共分3种情况：
            1）没有条件，无WHERE
            2）有一个条件，有WHERE无AND
            3）两个及以上，有WHERE 有 AND 
        let whereStr = ``
        let whereStr = `WHERE grade_name = '高一' `
        let whereStr = `WHERE grade_name = '高一' AND area_name = '北京'`
    */
     let whereStr = `` //默认什么条件也没有
    
 
     //let area = '河北' //如果area变量不为空，则代表要以地区进行筛选
     if( area != '' ){
       if( whereStr == '' ){
            whereStr += `WHERE area_name = '${area}'`
       }else{
        whereStr += ` AND area_name = '${area}'`
       }
     }  

     //let grade = '初一' //如果grade变量不为空，则代表要以年级进行筛选
     if( grade != '' ){
        if( whereStr == '' ){
           whereStr += `WHERE grade_name = '${grade}'`
        }else{
           whereStr += ` AND grade_name = '${grade}'`
        }       
    }

    if( subject != '' ){
        if( whereStr == '' ){
           whereStr += `WHERE subject_name = '${subject}'`
        }else{
           whereStr += ` AND subject_name = '${subject}'`
        }       
    }

    if( category != '' ){
        if( whereStr == '' ){
           whereStr += `WHERE category_name = '${category}'`
        }else{
           whereStr += ` AND category_name = '${category}'`
        }       
    } 

    //1. 写sql1,有limit的
    let sql1 = ` SELECT *,CONCAT('${API_URL}/uploads/courses/',image_src) AS image_src FROM e_course ${whereStr} LIMIT ${start},${pageSize} `
    let sql2 = ` SELECT * FROM e_course ${whereStr}`
   
    // res.send(sql1)
    // return
    //2. 执行sql
    let { result: result1 } = await MyQuery(sql1) //{ err,result }
    let { result: result2 } = await MyQuery(sql2) //{ err,result }
    let rows = result2.length //总条数
    let totalPage = Math.ceil(rows / pageSize) //总页码
    // res.send( result2.length + '' )
    // return
    //3. 要处理成规范、格式化的json数据
    //做分页
    data = {
        msg: 'OK',
        status: 200,
        result: {
            data: result1,
            page, //当前页码
            totalPage, //总页码
            rows,//总条数
        }
    }
    res.send(data)
})


//搜索接口
// SELECT * FROM e_course WHERE title LIKE '%keyword%'
router.get('/search', async (req, res, next) => {
    // let page = 2//要获取第几页的内容
    // let pageSize = 5//每页展示size条   keyword:搜索关键字
    let { page = 1, pageSize = 5,keyword='' } = req.query
    let data = {}//最终要返回的数据格式  msg,status,result

    if( keyword == '' ){
        data = {
            msg:'请输入关键字',
            status:500
        }
        res.send(data)
        return
    }

    // 1)数据处理
    page = page - 0 //parseInt / Number
    pageSize = pageSize - 0 //parseInt / Number
    // 2) 数据严格性
    if (!page || page <= 0) { //如果报错给默认值  
        page = 1
    }
    if (!pageSize || pageSize <= 0) {//如果报错给默认值
        pageSize = 5   
    }

    //分析分页
    let start = (page - 1) * pageSize //从第start条开始取

   

    //1. 写sql1,有limit的
    let sql1 = ` SELECT *,CONCAT('${API_URL}/uploads/courses/',image_src) AS image_src FROM e_course WHERE title LIKE '%${keyword}%' LIMIT ${start},${pageSize} `
    let sql2 = ` SELECT * FROM e_course WHERE title LIKE '%${keyword}%'`
   

    // res.send(sql1)
    // return
    //2. 执行sql
    let { result: result1 } = await MyQuery(sql1) //{ err,result }
    let { result: result2 } = await MyQuery(sql2) //{ err,result }
    let rows = result2.length //总条数
    let totalPage = Math.ceil(rows / pageSize) //总页码
    // res.send( result2.length + '' )
    // return
    //3. 要处理成规范、格式化的json数据
    //做分页
    data = {
        msg: 'OK',
        status: 200,
        result: {
            data: result1,
            page, //当前页码
            totalPage, //总页码
            rows,//总条数
            keyword,//搜索关键字
        }
    }
    res.send(data)
})
module.exports = router;
